package com.pls.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.pls.constants.dbConstants;
import com.pls.domain.Employee;
import com.pls.domain.EmployeeWorkstation;
import com.pls.domain.Seat;
import com.pls.domain.UserCredential;
import com.pls.utilities.ConnectionManager;


public class UserDAOImpl implements UserDAO {
	Logger logger = Logger.getLogger(UserDAOImpl.class);
	private EmployeeWorkstation empwork;
	private CallableStatement proc = null;
	private ResultSet rs = null;
	private ConnectionManager conManager;
	private Connection connection = null;
	
	@Override
	public EmployeeWorkstation userLoginByCredentials(String emailAddress, String password) throws Exception {
		logger.debug("Start of getting login credentials by username and password");
		empwork = new EmployeeWorkstation();
		
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_LOGIN_CREDENTIALS);
		      proc.setString(1, emailAddress);
		      proc.setString(2, password);
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
		      
		      empwork = populateEmployeeWorkstation(rs, empwork);
		      

		      rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
			   logger.error("SQLException: " + e.getMessage().toString());
				throw e;
				
				
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				logger.error("ClassNotFoundException: " + e.getMessage().toString());
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting login credentials by username and password");
		 return empwork;
		 
	}

	public EmployeeWorkstation userLoginByGoogle(String emailAddress) throws Exception{
		logger.debug("Start of getting login credentials by username and password");
		empwork = new EmployeeWorkstation();
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_LOGIN_CREDENTIALS_USING_GOOGLE);
		      proc.setString(1, emailAddress);
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
		      
		      empwork = populateEmployeeWorkstation(rs, empwork);
		      rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
				throw e;
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting login credentials by google mail");
		 return empwork;
	}
	
	private EmployeeWorkstation populateEmployeeWorkstation(ResultSet rs, EmployeeWorkstation empwork){
		
		
		try {
			while (rs.next())
			{
				Employee emp = new Employee();
				Seat seat = new Seat();
				emp.setEmployeeId(Integer.parseInt(rs.getString("employeeId")));
				emp.setFirstName(rs.getString("firstName"));
				emp.setLastName(rs.getString("lastName"));
				emp.setEmailAddress(rs.getString("emailAddress"));
				emp.setPositionCode(rs.getString("positionCode"));
				emp.setPhoto(rs.getString("photo"));
				emp.setUserRole(rs.getString("userRole"));
				seat.setSeatId(Integer.parseInt(rs.getString("seatId")));
				seat.setSeatCode(rs.getString("seatCode"));
				// seat.setSeatQuadrant(rs.getString("quadrant"));
				seat.setLocationDetailId(rs.getString("locationDetailId"));
				seat.setLocalNumber(Integer.parseInt(rs
						.getString("localNumber")));
				seat.setSeatTypeId(Integer.parseInt(rs.getString("seatTypeId")));
				seat.setSeatType(rs.getString("seatType"));
				seat.setSeatColumn(Integer.parseInt(rs.getString("seatColumn")));
				seat.setSeatRow(Integer.parseInt(rs.getString("seatRow")));
				empwork.setEmployee(emp);
				empwork.setSeatDetails(seat);
			 }
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			logger.error("NumberFormatException: " + e.getMessage().toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SQLException: " + e.getMessage().toString());
		}
		
		return empwork;
	}

}
